import pymysql

from model.Currm import Currm


class CurrmService:
    def create_currm(self, inp):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        idSql = "SELECT * FROM generate"
        generateUpdate = "update generate set num=%s"
        try:
            print(idSql)
            cursor.execute(idSql)
            currmId = cursor.fetchone()[0]
            print(generateUpdate % (currmId + 1))
            cursor.execute(generateUpdate % (currmId + 1))
            inp['id'] = currmId
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()

        sql = "INSERT INTO CURRM (id,name,teacher_name,plan,credit,time,position) VALUES ('%s', '%s', '%s','%s','%s', '%s','%s')"
        data = (inp['id'], inp['name'], inp['teacherName'], inp['plan'], inp['credit'], inp['time'], inp['position'])
        try:
            print(sql % data)
            cursor.execute(sql % data)
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()
        else:
            sqlConnect.commit()
            cursor.close()
            sqlConnect.close()
            return 'SUCCESS'

    def getById(self, id):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "SELECT * FROM CURRM WHERE id = %s"
        print(sql % id)
        cursor.execute(sql % id)
        try:
            result = cursor.fetchone()
            entity = Currm.result_to_entity(result)
        except:
            return '未找到id'
        cursor.close()
        sqlConnect.close()
        return entity

    def list(self):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "SELECT * FROM CURRM"
        print(sql)
        cursor.execute(sql)
        try:
            result = cursor.fetchall()
            studentList = list(map(Currm.result_to_entity_dict, result))
        except:
            return 'sql错误'
        cursor.close()
        sqlConnect.close()
        return studentList

    def delete(self, currm):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "DELETE FROM CURRM WHERE id = %s"
        sql = sql % currm.id
        print(sql)
        try:
            cursor.execute(sql)
        except:
            return 'sql错误'
        sqlConnect.commit()
        cursor.close()
        sqlConnect.close()
        return True

    def update(self, inp, currmId):
        sqlConnect = pymysql.Connect(
            host='localhost',
            port=3306,
            user='root',
            passwd='123456',
            db='manager',
            charset='utf8'
        )
        cursor = sqlConnect.cursor()
        sql = "UPDATE CURRM SET name='%s', teacher_name='%s', plan='%s', credit='%s',time='%s',position='%s' WHERE id = %s"
        data = (inp['name'], inp['teacherName'], inp['plan'], inp['credit'], inp['time'], inp['position'], currmId)
        try:
            print(sql % data)
            cursor.execute(sql % data)
        except Exception as e:
            sqlConnect.rollback()
            return 'sql执行错误' + e.__str__()
        else:
            sqlConnect.commit()
            cursor.close()
            sqlConnect.close()
            return 'SUCCESS'
